﻿/******************************************************   
创建者: 王东杰
创建时间: 2017-04-21
修改:替换查询店铺控件
修改时间: 2017-05-25
功能: 小品类到颜色SKC数量
修改时间: 2017-07-20
功能: 增加多主体
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using System.Data;
using com.nm.library.Helper;
using com.nm.Helper;
using george.Helper;
using NDF.Utilities;
using com.nm.library;

namespace PlanAPI
{
    [Description("小品类到颜色SKC数量")]
    public class PlItemColorSkcHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();


        #region 获取数据库中的数据,不计算

        [MustLogin]
        [Description("小品类到颜色SKC数量列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLItemColorSkcModel.ListModel>>))]
        public ApiResult GetList(
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")]string KindID = "",
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("品类")]string ModelID = "",
            [ParamDescription("小品类")]string ItemID = "",
            [ParamDescription("款")]string DataFlag = "", 
            [ParamDescription("店铺shopid")]string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (set == null) return Error("没有要修改企划的月份");
            var count = ReturnCount(false, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID,DataFlag, ShopIDs.GetString());
            var dt = ReturnList(false, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID,DataFlag, ShopIDs.GetString(), Page, Size);
            var result = PaperData<IGrouping<string, PLItemColorSkcModel.ListModel>>.Create(count, Size, Page);
            var list = ModelConvertHelper<PLItemColorSkcModel.ListModel>.ConvertToModel(dt);

            result.Items =
                                    list.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                            d.ItemID + "_" + d.DataFlag + "_" + d.CkSKCQty + "_" + d.Weekv
                                            ).ToList();
            return Success(result);
        }

        #endregion 获取数据库中的数据,不计算

        #region 获取数据库中的数据,并进行计算

        [MustLogin]
        [Description("小品类到价格带SKC数量列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLItemColorSkcModel.ListModel>>))]
        public ApiResult GetComputedList(
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")]string KindID = "",
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("品类")]string ModelID = "",
            [ParamDescription("小品类")]string ItemID = "",
            [ParamDescription("款")]string DataFlag = "",
            [ParamDescription("店铺shopid")]string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (set == null) return Error("没有要修改企划的月份");
            var count = ReturnCount(true, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID,DataFlag, ShopIDs.GetString());
            var dt = ReturnList(true, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, DataFlag, ShopIDs.GetString(), Page, Size);
            //删除数据库中的数据
            var cmdList = DeleteComputedList2DB(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID,DataFlag, ShopIDs.GetString());
            Helper.ExecuteSqlTran(cmdList);
            if (dt?.Rows.Count > 0)
            {
                var result = PaperData<IGrouping<string, PLItemColorSkcModel.ListModel>>.Create(count, count, Page);
                var list = ModelConvertHelper<PLItemColorSkcModel.ListModel>.ConvertToModel(dt);

                result.Items =
                    list.GroupBy(
                        d =>
                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                            d.ItemID + "_" + d.DataFlag + "_" + d.PriceColorName
                    ).ToList();
                ComputeAll(result);

                var newList = result.Items.SelectMany(p => p).ToList();
                //插入到数据库
                InsertComputedList2DB(newList);
            }
            var resultList = GetList(ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID,DataFlag, ShopIDs, Page, Size);
            return resultList;
        }

        #endregion 获取数据库中的数据,并进行计算

        #region 计算辅助方法

        #region 全部计算

        public void ComputeAll(PaperData<IGrouping<string, PLItemColorSkcModel.ListModel>> result)
        {
            //计算SKC合计,TotalQty
            ComputeTotalQty(result.Items);
            //计算skc数 和 计算结果,差额
            ComputeCalcQty(result.Items);
            //计算增加/扣减
            result.Items = ComputeAddQty(result.Items);
            //计算skc数
            ComputePirceColorSKCQty(result.Items);
        }
        #endregion 全部计算

        #region 计算SKC合计

        /// <summary>
        /// 2、第一周SKC合计＝第一周-短T+0;第二周SKC合计=第二周-短T+第一周-短Ｔ;如表格:20+30=50;第三，第四以此类推(前几周的合计);
        /// </summary>
        /// <param name="items"></param>
        public void ComputeTotalQty(IList<IGrouping<string, PLItemColorSkcModel.ListModel>> items)
        {
            //更新每周的SKC合计
            foreach (var row in items)
            {
                int count = 0;
                //上一周的SKC合计,有可能不是连续的周
                int previousTotalQty = 0;
                foreach (var item in row)
                {
                    if (0 == count)
                    {
                        //获取第一周
                        item.TotalQty = item.CkSKCQty;
                    }
                    else
                    {

                        item.TotalQty = item.CkSKCQty + previousTotalQty;
                    }
                    previousTotalQty = item.TotalQty;
                    count++;
                }
            }
        }

        #endregion 计算SKC合计

        #region 计算skc数 和 计算结果,差额


        /// <summary>
        /// 3、计算skc数=skc合计*价格带占比 ；保留两位小数 ;
        /// 4、计算结果=计算skc数四舍五入，取整；
        /// 5、差额=计算结果－计算skc数；
        /// </summary>
        /// <param name="items"></param>
        public void ComputeCalcQty(IList<IGrouping<string, PLItemColorSkcModel.ListModel>> items)
        {
            //更新计算skc数 和 计算结果,差额
            for (int i = 0; i < items.Count; i++)
            {
                items[i].ToList().ForEach(
                       (p) =>
                       {
                           p.CalcQty = Convert.ToDecimal(MathHelper.Rounding(Convert.ToDouble(p.TotalQty * p.CkPtStandAmountRate), 2));
                           p.RoundQty = int.Parse(MathHelper.Rounding(Convert.ToDouble(p.CalcQty), 0).ToString());
                           p.DiffValue = p.RoundQty - p.CalcQty;
                       }
                    );
            }
        }

        #endregion 计算skc数 和 计算结果,差额

        #region 计算增加/扣减

        /// <summary>
        /// 增加/扣减 计算
        /// </summary>
        /// <param name="items"></param>
        public IList<IGrouping<string, PLItemColorSkcModel.ListModel>> ComputeAddQty(IList<IGrouping<string, PLItemColorSkcModel.ListModel>> list)
        {
            //重新分组,以方便计算
            var newList = list.SelectMany(p => p).ToList();
            var items = newList.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                            d.ItemID + "_" + d.DataFlag + "_" + d.CkSKCQty + "_" + d.Weekv
                                            ).ToList();
            //增加/扣减 计算
            for (int i = 0; i < items.Count; i++)
            {
                //先清除原有的增加/扣减数据
                items[i].ForEach(p => p.AddQty = 0);
                var result = items[i].Sum(p => p.RoundQty) - items[i].FirstOrDefault().TotalQty;
                //判断以周为单位的计算结果RoundQty合计 和 SKC合计TotalQty 的大小关系

                //等于
                if (result == 0)
                {
                    items[i].ToList().ForEach(p => p.AddQty = 0);
                }
                //大于
                else if (result > 0)
                {
                    //8、合计>SKC数时，以周为单位，流行色减１；
                    var firstOrDefault = items[i].ToList().FirstOrDefault(p => p.PriceColorName == "流行色");
                    if (firstOrDefault != null)
                        firstOrDefault.AddQty = -1;

                }
                //小于
                else
                {
                    //9、合计 < SKC数时，以周为单位，非流行色加1；
                    var firstOrDefault = items[i].ToList().FirstOrDefault(p => p.PriceColorName == "基本色");
                    if (firstOrDefault != null)
                        firstOrDefault.AddQty = 1;

                }
            }
            //计算完毕,还原分组.方便下次运算
            var tempList2 = items.SelectMany(p => p).ToList();
            var itemsGrouped = tempList2.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                             d.ItemID + "_" + d.DataFlag + "_" + d.PriceColorName
                                            ).ToList();
            return itemsGrouped;
        }

        #endregion 计算增加/扣减

        #region 计算skc数
        /// <summary>
        /// 9、计算PriceColorSKCQty SKC数
        /// </summary>
        /// <param name="items"></param>
        public void ComputePirceColorSKCQty(IList<IGrouping<string, PLItemColorSkcModel.ListModel>> items)
        {
            //更新计算skc数
            foreach (var row in items)
            {
                int count = 0;
                //之前周的SKC合计,有可能不是连续的周
                int previousTotalQtySum = 0;
                foreach (var item in row)
                {
                    if (0 == count)
                    {
                        //获取第一周
                        item.PriceColorSKCQty = item.RoundQty + item.AddQty;
                    }
                    else
                    {
                        //为防止skc数出现负数,计算时先判断,
                        //当前价格带的计算结果+增加/扣减-前N周skc的合计 > 0
                        //如果出现负数.就重置为0
                        if ((item.RoundQty + item.AddQty - previousTotalQtySum) > 0)
                        {
                            item.PriceColorSKCQty = item.RoundQty + item.AddQty - previousTotalQtySum;
                        }
                        else
                        {
                            item.PriceColorSKCQty = 0;
                        }
                    }
                    //保存本周的PriceColorSKCQty到临时变量
                    previousTotalQtySum += item.PriceColorSKCQty;
                    count++;
                }
            }
        }


        #endregion 计算skc数

        #endregion 计算辅助方法

        #region 返回数据列表

        /// <summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>        /// 返回小品类到价格带SKC数量
        /// </summary>

        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public DataTable ReturnList(bool IsCompute, long Yearv, long Monthv, string ParentAreaID, string KindID,
            string SeriesID, string MaterialProperty, string ModelID, string ItemID,string DataFlag, string ShopIDs, int Page, int Size)
        {
            try
            {

                var strSql = string.Empty;
                if (!IsCompute)
                {
                    #region 查询时返回数据列表



                    //如果只是展示列表,就查询出所有系列的数据,包含推品

                    strSql = $@"
 
SELECT
			sp.yearv,
			sp.monthv,
			sp.parentareaid,
			shop.shopname AS ShopName,
			kind.kindname AS KindName,
			series.seriesname AS SeriesName,
			sp.materialproperty,
			model.modelname AS ModelName,
			sp.weekv,
			item.itemname AS ItemName,
			sp.ckskcqty,
			sp.PriceColorName,
			sp.CkPtStandAmountRate,
			sp.seriesid,
			sp.itemid,
			shop.shopcode AS ShopCode,
			sp.shopid,
			sp.kindid,
			sp.modelid,
            sp.DeepQty as DeepQty,
			sp.totalqty,
			sp.calcqty,
			sp.roundqty,
			sp.diffvalue,
			sp.addqty,
			sp.pricecolorskcqty,
            sp.DataFlag
		FROM
			ngoods.ngoods_pl_item_skcforpcDetail sp
		LEFT JOIN ndw.ndw_shop shop ON
		 sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID    and 
        sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON
		 sp.ExtrSource = dict.ExtrSource and sp.CompanyID=dict.CompanyID    and 
			sp.parentareaid = dict.dictvalue
		LEFT JOIN nods.nods_erp_sd_mat_kind kind ON
		 sp.ExtrSource = kind.ExtrSource and 	sp.kindid = kind.kindid
		LEFT JOIN nods.nods_erp_sd_mat_model model ON
		 sp.ExtrSource = model.ExtrSource and 	sp.modelid = model.modelid
		LEFT JOIN nods.nods_erp_sd_mat_series series ON
		 sp.ExtrSource = series.ExtrSource and 	sp.seriesid = series.seriesid
		LEFT JOIN nods.nods_erp_sd_mat_item item ON
		 sp.ExtrSource = item.ExtrSource and 	sp.itemid = item.itemid
		WHERE
sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID='{UserInfo.UserCompanyId}' and 
		sp.priceorcolor = 1 and
			sp.ckskcqty > 0 and
			
			
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID = '{ParentAreaID}' ")}
{ (string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
                    { (string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and sp.DataFlag='{DataFlag}' ")}
                    {
                        (string.IsNullOrWhiteSpace(ShopIDs)
                                               ? ""
                                               : $" and  shop.shopid in {ShopIDs}  ")}


                    order by

    Yearv,
	Monthv,
	ParentAreaID,
	ShopID,
	KindID,
	MaterialProperty,
	ModelID,
	SeriesID,
	ItemName,	 
    DataFlag,
	Weekv,
    PriceColorName desc

";
                    strSql += $" LIMIT {Size} offset {(Page - 1.0) * Size}	";

                    #endregion 查询时返回数据列表
                }
                else
                {
                    #region 计算时返回数据列表


                    strSql = $@"
SELECT
			sp.yearv,
			sp.monthv,
			sp.parentareaid,
			shop.shopname AS ShopName,
			kind.kindname AS KindName,
			series.seriesname AS SeriesName,
			sp.materialproperty,
			model.modelname AS ModelName,
			sp.weekv,
			item.itemname AS ItemName,
			sum(sp.pricecolorskcqty) as ckskcqty,
			'流行色' AS PriceColorName,
			IFNULL(min( cr.popularrate ), '0.3' ) AS CkPtStandAmountRate,
			sp.seriesid,
			sp.itemid,
			shop.shopcode AS ShopCode,
			sp.shopid,
			sp.kindid,
			sp.modelid,
            min(sp.DeepQty) as DeepQty,
			min(sp.totalqty) as totalqty ,
			min(sp.calcqty) as calcqty ,
			min(sp.roundqty) as roundqty ,
			min(sp.diffvalue) as diffvalue ,
			min(sp.addqty) as addqty,
			min(sp.pricecolorskcqty) as pricecolorskcqty,
            sp.DataFlag
		FROM
			ngoods.ngoods_pl_item_skcforpcDetail sp
		LEFT JOIN ngoods.ngoods_pl_bas_seasonmonth sm ON
 sp.ExtrSource = sm.ExtrSource and sp.CompanyID=sm.CompanyID    and 
			sp.companyid = sm.companyid
			AND sp.monthv = sm.monthv
			AND sp.parentareaid = sm.parentareaid
		LEFT JOIN ngoods.ngoods_pl_bas_shopcolorrate AS cr ON
 sp.ExtrSource = cr.ExtrSource and sp.CompanyID=cr.CompanyID    and 
			sp.companyid = cr.companyid
			AND sp.yearv = cr.yearv
			AND sm.seasonid = cr.seasonid
			AND sp.parentareaid = cr.parentareaid
			AND sp.shopid = cr.shopid
		LEFT JOIN ndw.ndw_shop shop ON
 sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID    and 
			sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON
 sp.ExtrSource = dict.ExtrSource and sp.CompanyID=dict.CompanyID    and 
			sp.parentareaid = dict.dictvalue
		LEFT JOIN nods.nods_erp_sd_mat_kind kind ON
		 sp.ExtrSource = kind.ExtrSource and sp.kindid = kind.kindid
		LEFT JOIN nods.nods_erp_sd_mat_model model ON
		 sp.ExtrSource = model.ExtrSource and 	sp.modelid = model.modelid
		LEFT JOIN nods.nods_erp_sd_mat_series series ON
		 sp.ExtrSource = series.ExtrSource and 	sp.seriesid = series.seriesid
		LEFT JOIN nods.nods_erp_sd_mat_item item ON
		 sp.ExtrSource = item.ExtrSource and 	sp.itemid = item.itemid
		WHERE
sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID='{UserInfo.UserCompanyId}' and 
		sp.priceorcolor = {(IsCompute ? 0 : 1)} and
			sp.ckskcqty > 0 and	
			
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
{(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and sp.DataFlag='{DataFlag}' ")}
{(string.IsNullOrWhiteSpace(ShopIDs)
                        ? ""
                        : $" and  shop.shopid in {ShopIDs}  ")} 
		

group by 
sp.yearv,
sp.monthv,
sp.parentareaid,
sp.materialproperty,
sp.weekv,
sp.seriesid,
sp.itemid,
sp.DataFlag,
sp.shopid,
sp.kindid,
sp.modelid,
shop.shopname,
kind.kindname,
series.seriesname,
model.modelname,
item.itemname,
shop.shopcode

UNION

SELECT
			sp.yearv,
			sp.monthv,
			sp.parentareaid,
			shop.shopname AS ShopName,
			kind.kindname AS KindName,
			series.seriesname AS SeriesName,
			sp.materialproperty,
			model.modelname AS ModelName,
			sp.weekv,
			item.itemname AS ItemName,
			sum(sp.pricecolorskcqty) as ckskcqty,
			'基本色' AS PriceColorName,
			IFNULL(min( cr.basicrate ), '0.7' ) AS CkPtStandAmountRate,
			sp.seriesid,
			sp.itemid,
			shop.shopcode AS ShopCode,
			sp.shopid,
			sp.kindid,
			sp.modelid,
            min(sp.DeepQty) as DeepQty,
			min(sp.totalqty) as totalqty ,
			min(sp.calcqty) as calcqty ,
			min(sp.roundqty) as roundqty ,
			min(sp.diffvalue) as diffvalue ,
			min(sp.addqty) as addqty,
			min(sp.pricecolorskcqty) as pricecolorskcqty,
            sp.DataFlag
		FROM
			ngoods.ngoods_pl_item_skcforpcDetail sp
		LEFT JOIN ngoods.ngoods_pl_bas_seasonmonth sm ON
 sp.ExtrSource = sm.ExtrSource and sp.CompanyID=sm.CompanyID    and 
			sp.companyid = sm.companyid
			AND sp.monthv = sm.monthv
			AND sp.parentareaid = sm.parentareaid
		LEFT JOIN ngoods.ngoods_pl_bas_shopcolorrate AS cr ON
 sp.ExtrSource = cr.ExtrSource and sp.CompanyID=cr.CompanyID    and 
			sp.companyid = cr.companyid
			AND sp.yearv = cr.yearv
			AND sm.seasonid = cr.seasonid
			AND sp.parentareaid = cr.parentareaid
			AND sp.shopid = cr.shopid
		LEFT JOIN ndw.ndw_shop shop ON
 sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID    and 
			sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON
		 sp.ExtrSource = shop.ExtrSource and 	sp.parentareaid = dict.dictvalue
		LEFT JOIN nods.nods_erp_sd_mat_kind kind ON
			 sp.ExtrSource = kind.ExtrSource and sp.kindid = kind.kindid
		LEFT JOIN nods.nods_erp_sd_mat_model model ON
		 sp.ExtrSource = model.ExtrSource and 	sp.modelid = model.modelid
		LEFT JOIN nods.nods_erp_sd_mat_series series ON
		 sp.ExtrSource = series.ExtrSource and 	sp.seriesid = series.seriesid
		LEFT JOIN nods.nods_erp_sd_mat_item item ON
		 sp.ExtrSource = item.ExtrSource and 	sp.itemid = item.itemid
		WHERE
sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID='{UserInfo.UserCompanyId}' and 
		sp.priceorcolor = {(IsCompute ? 0 : 1)} and
			sp.ckskcqty > 0 and
			
			
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
{(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and sp.DataFlag='{DataFlag}' ")}
{(string.IsNullOrWhiteSpace(ShopIDs)
                        ? ""
                        : $" and  shop.shopid in {ShopIDs}  ")} 
		

group by 
sp.yearv,
sp.monthv,
sp.parentareaid,
sp.materialproperty,
sp.weekv,
sp.seriesid,
sp.itemid,
sp.shopid,
sp.kindid,
sp.modelid,
sp.DataFlag,
shop.shopname,
kind.kindname,
series.seriesname,
model.modelname,
item.itemname,
shop.shopcode

                    order by

    Yearv,
	Monthv,
	ParentAreaID,
	ShopID,
	KindID,
	MaterialProperty,
	ModelID,
	SeriesID,
	ItemName,	 
    DataFlag,
	Weekv


";

                    #endregion 计算时返回数据列表
                }
                var cmd = Helper.GetSqlStringCommand(strSql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion 返回数据列表

        #region 返回行数
        /// <summary>
        /// 返回小品类到价格带SKC数量总条数
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public int ReturnCount(bool IsCompute, long Yearv, long Monthv, 
            string ParentAreaID, string KindID, string SeriesID, 
            string MaterialProperty, string ModelID, 
            string ItemID,string DataFlag, string ShopIDs)
        {
            try
            {
                var countSql = string.Empty;
                if (IsCompute)
                {
                    #region 计算时返回的行数

                    countSql = $@"

select count(1) as count from (SELECT
			sp.yearv,
			sp.monthv,
			sp.parentareaid,
			shop.shopname AS ShopName,
			kind.kindname AS KindName,
			series.seriesname AS SeriesName,
			sp.materialproperty,
			model.modelname AS ModelName,
			sp.weekv,
			item.itemname AS ItemName,
			sum(sp.pricecolorskcqty) as ckskcqty,
			'流行色' AS PriceColorName,
			IFNULL(min( cr.popularrate ), '0.3' ) AS CkPtStandAmountRate,
			sp.seriesid,
			sp.itemid,
			shop.shopcode AS ShopCode,
			sp.shopid,
			sp.kindid,
			sp.modelid,
            min(sp.DeepQty) as DeepQty,
			min(sp.totalqty) as totalqty ,
			min(sp.calcqty) as calcqty ,
			min(sp.roundqty) as roundqty ,
			min(sp.diffvalue) as diffvalue ,
			min(sp.addqty) as addqty,
			min(sp.pricecolorskcqty) as pricecolorskcqty,
            sp.DataFlag
		FROM
			ngoods.ngoods_pl_item_skcforpcDetail sp
		LEFT JOIN ngoods.ngoods_pl_bas_seasonmonth sm ON
 sp.ExtrSource = sm.ExtrSource and sp.CompanyID=sm.CompanyID    and 
			sp.companyid = sm.companyid
			AND sp.monthv = sm.monthv
			AND sp.parentareaid = sm.parentareaid
		LEFT JOIN ngoods.ngoods_pl_bas_shopcolorrate AS cr ON
  sp.ExtrSource = cr.ExtrSource and sp.CompanyID=cr.CompanyID    and
			sp.companyid = cr.companyid
			AND sp.yearv = cr.yearv
			AND sm.seasonid = cr.seasonid
			AND sp.parentareaid = cr.parentareaid
			AND sp.shopid = cr.shopid
		LEFT JOIN ndw.ndw_shop shop ON
   sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID   and
			sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON
dict.ExtrSource = shop.ExtrSource and dict.CompanyID=shop.CompanyID    and  
			sp.parentareaid = dict.dictvalue
		LEFT JOIN nods.nods_erp_sd_mat_kind kind ON
		sp.ExtrSource = kind.ExtrSource and 	sp.kindid = kind.kindid
		LEFT JOIN nods.nods_erp_sd_mat_model model ON
		sp.ExtrSource = model.ExtrSource and 	sp.modelid = model.modelid
		LEFT JOIN nods.nods_erp_sd_mat_series series ON
		sp.ExtrSource = series.ExtrSource and 	sp.seriesid = series.seriesid
		LEFT JOIN nods.nods_erp_sd_mat_item item ON
		sp.ExtrSource = item.ExtrSource and 	sp.itemid = item.itemid
		WHERE
sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID='{UserInfo.UserCompanyId}' and 
		sp.priceorcolor = 0 and
			sp.ckskcqty > 0 and
			
			
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
{(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and sp.DataFlag='{DataFlag}' ")}
{(string.IsNullOrWhiteSpace(ShopIDs)
                       ? ""
                       : $" and  shop.shopid in {ShopIDs}  ")} 
		

group by 
sp.yearv,
sp.monthv,
sp.parentareaid,
sp.materialproperty,
sp.weekv,
sp.seriesid,
sp.itemid,
sp.DataFlag,
sp.shopid,
sp.kindid,
sp.modelid,
shop.shopname,
kind.kindname,
series.seriesname,
model.modelname,
item.itemname,
shop.shopcode

UNION

SELECT
			sp.yearv,
			sp.monthv,
			sp.parentareaid,
			shop.shopname AS ShopName,
			kind.kindname AS KindName,
			series.seriesname AS SeriesName,
			sp.materialproperty,
			model.modelname AS ModelName,
			sp.weekv,
			item.itemname AS ItemName,
			sum(sp.pricecolorskcqty) as ckskcqty,
			'基本色' AS PriceColorName,
			IFNULL(min( cr.basicrate ), '0.7' ) AS CkPtStandAmountRate,
			sp.seriesid,
			sp.itemid,
			shop.shopcode AS ShopCode,
			sp.shopid,
			sp.kindid,
			sp.modelid,
            min(sp.DeepQty) as DeepQty,
			min(sp.totalqty) as totalqty ,
			min(sp.calcqty) as calcqty ,
			min(sp.roundqty) as roundqty ,
			min(sp.diffvalue) as diffvalue ,
			min(sp.addqty) as addqty,
			min(sp.pricecolorskcqty) as pricecolorskcqty,
            sp.DataFlag
		FROM
			ngoods.ngoods_pl_item_skcforpcDetail sp
		LEFT JOIN ngoods.ngoods_pl_bas_seasonmonth sm ON
 sp.ExtrSource = sm.ExtrSource and sp.CompanyID=sm.CompanyID    and 
			sp.companyid = sm.companyid
			AND sp.monthv = sm.monthv
			AND sp.parentareaid = sm.parentareaid
		LEFT JOIN ngoods.ngoods_pl_bas_shopcolorrate AS cr ON
 sp.ExtrSource = cr.ExtrSource and sp.CompanyID=cr.CompanyID    and 
			sp.companyid = cr.companyid
			AND sp.yearv = cr.yearv
			AND sm.seasonid = cr.seasonid
			AND sp.parentareaid = cr.parentareaid
			AND sp.shopid = cr.shopid
		LEFT JOIN ndw.ndw_shop shop ON
 sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID    and 
			sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON
 sp.ExtrSource = dict.ExtrSource and sp.CompanyID=dict.CompanyID    and 
			sp.parentareaid = dict.dictvalue
		LEFT JOIN nods.nods_erp_sd_mat_kind kind ON
		sp.ExtrSource = kind.ExtrSource and 	sp.kindid = kind.kindid
		LEFT JOIN nods.nods_erp_sd_mat_model model ON
		sp.ExtrSource = model.ExtrSource and 	sp.modelid = model.modelid
		LEFT JOIN nods.nods_erp_sd_mat_series series ON
		sp.ExtrSource = series.ExtrSource and 	sp.seriesid = series.seriesid
		LEFT JOIN nods.nods_erp_sd_mat_item item ON
		sp.ExtrSource = item.ExtrSource and 	sp.itemid = item.itemid
		WHERE
sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID='{UserInfo.UserCompanyId}' and 
		sp.priceorcolor = 0 and
			sp.ckskcqty > 0 and
			
			
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
{(string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and sp.DataFlag='{DataFlag}' ")}
{(string.IsNullOrWhiteSpace(ShopIDs)
                       ? ""
                       : $" and  shop.shopid in {ShopIDs}  ")} 
		

group by 
sp.yearv,
sp.monthv,
sp.parentareaid,
sp.materialproperty,
sp.weekv,
sp.seriesid,
sp.itemid,
sp.DataFlag,
sp.shopid,
sp.kindid,
sp.modelid,
shop.shopname,
kind.kindname,
series.seriesname,
model.modelname,
item.itemname,
shop.shopcode

) as t

";
                    #endregion 计算时返回的行数
                }
                else
                {
                    #region 查询时返回的行数

                    countSql = $@"

select count(1) as count from(
SELECT
			sp.yearv,
			sp.monthv,
			sp.parentareaid,
			shop.shopname AS ShopName,
			kind.kindname AS KindName,
			series.seriesname AS SeriesName,
			sp.materialproperty,
			model.modelname AS ModelName,
			sp.weekv,
			item.itemname AS ItemName,
			sp.ckskcqty,
			sp.PriceColorName,
			sp.CkPtStandAmountRate,
			sp.seriesid,
			sp.itemid,
			shop.shopcode AS ShopCode,
			sp.shopid,
			sp.kindid,
			sp.modelid,
            sp.DeepQty as DeepQty,
			sp.totalqty,
			sp.calcqty,
			sp.roundqty,
			sp.diffvalue,
			sp.addqty,
			sp.pricecolorskcqty
		FROM
			ngoods.ngoods_pl_item_skcforpcDetail sp
		LEFT JOIN ndw.ndw_shop shop ON
 sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID    and 
			sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON
 sp.ExtrSource = dict.ExtrSource and sp.CompanyID=dict.CompanyID    and 
			sp.parentareaid = dict.dictvalue
		LEFT JOIN nods.nods_erp_sd_mat_kind kind ON
		 sp.ExtrSource = kind.ExtrSource and	sp.kindid = kind.kindid
		LEFT JOIN nods.nods_erp_sd_mat_model model ON
		 sp.ExtrSource = model.ExtrSource and	sp.modelid = model.modelid
		LEFT JOIN nods.nods_erp_sd_mat_series series ON
		 sp.ExtrSource = series.ExtrSource and	sp.seriesid = series.seriesid
		LEFT JOIN nods.nods_erp_sd_mat_item item ON
		 sp.ExtrSource = item.ExtrSource and	sp.itemid = item.itemid
		WHERE
sp.ExtrSource = '{UserInfo.UserExtrSource}' and sp.CompanyID='{UserInfo.UserCompanyId}' and 
		sp.priceorcolor = 1 and
			sp.ckskcqty > 0 and
			
			
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID = '{ParentAreaID}' ")}
{ (string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
                    { (string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and sp.DataFlag='{DataFlag}' ")}
                    {
                        (string.IsNullOrWhiteSpace(ShopIDs)
                                               ? ""
                                               : $" and  shop.shopid in {ShopIDs}  ")}

) as t

";

                    #endregion 查询时返回的行数
                }

                var cmd = Helper.GetSqlStringCommand(countSql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion 返回行数

        #region 删除所有的计算结果到数据库
        /// <summary>
        /// 保存所有的计算结果到数据库
        /// </summary>
        /// <param name="list"></param>
        public List<CommandInfo> DeleteComputedList2DB(long Yearv, long Monthv, string ParentAreaID, 
            string KindID,string SeriesID, string MaterialProperty, 
            string ModelID, string ItemID, string DataFlag,string ShopIDs)
        {

            var cmdList = new List<CommandInfo>();
            #region 删除

            var DeleteSql = $@"

DELETE
from
	ngoods.ngoods_PL_Item_SkcForPCDetail
where
ExtrSource = '{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and 
	   Yearv = '{Yearv}'
	                and Monthv = '{Monthv}'
	                and PriceOrColor = '1' 
                    { (string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and ParentAreaID = '{ParentAreaID}' ")}
                    { (string.IsNullOrWhiteSpace(KindID) ? "" : $" and KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and ItemID='{ItemID}' ")}
                    { (string.IsNullOrWhiteSpace(DataFlag) ? "" : $" and DataFlag='{DataFlag}' ")}
					                    { (string.IsNullOrWhiteSpace(ShopIDs) ? "" : $@" 	and ShopID In {ShopIDs}")}
                    ";

            #endregion 删除
            var cmdSave = new CommandInfo()
            {
                CommandText = DeleteSql
            };
            cmdList.Add(cmdSave);
            return cmdList;
        }
        #endregion 删除所有的计算结果到数据库

        #region 保存所有的计算结果到数据库
        /// <summary>
        /// 保存所有的计算结果到数据库
        /// </summary>
        /// <param name="list"></param>
        public void InsertComputedList2DB(IList<PLItemColorSkcModel.ListModel> list)
        {
            if (list != null && list.Count() > 0)
            {
                var cmdList = new List<CommandInfo>();
                var dtInsert = DataTableHelper.LinqToDataTable(list.Select(
                   p => new
                   {
                       p.Yearv,
                       p.Monthv,
                       p.Weekv,
                       p.ParentAreaID,
                       p.ShopID,
                       p.PriceOrColor,
                       p.KindID,
                       p.SeriesID,
                       p.MaterialProperty,
                       p.ModelID,
                       p.ItemID,
                       p.DataFlag,
                       p.DeepQty,
                       p.CkSKCQty,
                       p.TotalQty,
                       p.PriceColorName,
                       p.CkPtStandAmountRate,
                       p.CalcQty,
                       p.RoundQty,
                       p.DiffValue,
                       p.AddQty,
                       p.PriceColorSKCQty,
                       p.CompanyID,
                       p.ExtrSource,
                       p.MofifyDTM,
                   }
                   ).ToList());
                Helper.ExecuteSqlTran(cmdList, dtInsert, "ngoods.ngoods_PL_Item_SkcForPCDetail");
            }
        }
        #endregion 保存所有的计算结果到数据库

    }
}
